<?php

namespace App\Http\Controllers\Admin;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;

class StatisticsController extends Controller
{
    // echarts 合格数量 与 不合格数量 -- 按日期统计
    public function statisticsByDate(Request $request) {
        if($request->isMethod('POST')) {
            $date_start = $request->date_start;
            $date_end = $request->date_end;
        }
        else {
            $date_start = date("Y-m-d", strtotime('-1 years'));
            $date_end = date("Y-m-d");
        }
        $rbatchs = $request->input('batchs', '');
        if($batchs = $rbatchs) {
            $batchs = explode('，', $batchs);
        }
        //不合格项目统计
        $res0Item = DB::table('a_item_data as a')
                      ->leftJoin('a_contract_gaiyao as b', 'a.contract_gaiyao_id', '=', 'b.id')
                      ->leftJoin('a_testitem as c', 'a.item_id', '=', 'c.id')
                      ->leftJoin('a_contract as d', 'a.contract_id', '=', 'd.id')
                      ->whereBetween('a.utime', [$date_start, $date_end])
                      ->where('a.res', 0)
                      ->where('d.is_del', 0)
                      ->where(function($query) use ($batchs) {
                          if($batchs) {
                              $query->whereIn('d.batchNum', $batchs);
                          }
                      })
                      ->select('b.tool_name', 'c.name as item_name')
                      ->get();
        $res0Data = [];
        foreach($res0Item as $val) {
            if(isset($res0Data[$val->tool_name][$val->item_name])) {
                $res0Data[$val->tool_name][$val->item_name] += 1;
            }
            else {
                $res0Data[$val->tool_name][$val->item_name] = 1;
            }
        }
        $ret0Data = [];
        foreach($res0Data as $toolnameKey => $itemVal) {
            foreach($itemVal as $item => $count) {
                if(isset($ret0Data[$toolnameKey])) {
                    $ret0Data[$toolnameKey] .= "，".$item.":".$count;
                }
                else {
                    $ret0Data[$toolnameKey] = $item.":".$count;
                }
            }
        }
        //按日期统计
        //        DB::connection()->enableQueryLog();#开启执行日志
        $data = DB::table('a_sample_info AS A')->leftJoin('a_contract_gaiyao AS B', 'A.contract_gaiyao_id', '=', 'B.id')->leftJoin('a_contract AS C', 'A.contract_id', '=', 'C.id')->whereBetween('A.test_time', [$date_start, $date_end])->where(function($query) use ($batchs) {
            if($batchs) {
                $query->whereIn('C.batchNum', $batchs);
            }
        })->select('B.tool_name', DB::raw('Count( res > 0 OR NULL ) AS total,
                    Count( res = 1 OR NULL ) AS passed,
                    Count( res = 2 OR NULL ) AS unpassed,
                    FORMAT( Count( res = 1 OR NULL ) * 100 / Count( res > 0 OR NULL ), 2 ) AS lv'))->groupBy('B.tool_name')->orderByRaw('total desc,unpassed desc,tool_name desc')->get();
        //        dd(DB::getQueryLog());

        $count = count($data);
        return view('admin.statistics.statisticsByDate', compact('data', 'date_start', 'date_end', 'count', 'ret0Data', 'rbatchs'));
    }

    // echarts 合格数量 与 不合格数量 -- 按日期统计ajax
    public function statisticsByDateAjax(Request $request) {
        $date_start = $request->start;
        $date_end = $request->end;
        $rbatchs = $request->input('batchs', '');
        if($batchs = $rbatchs) {
            $batchs = explode('，', $batchs);
        }
        $data = DB::table('a_sample_info AS A')
                  ->leftJoin('a_contract_gaiyao AS B', 'A.contract_gaiyao_id', '=', 'B.id')
                  ->leftJoin('a_contract AS C', 'A.contract_id', '=', 'C.id')
                  ->whereBetween('A.test_time', [$date_start, $date_end])
                  ->where(function($query) use ($batchs) {
                      if($batchs) {
                          $query->whereIn('C.batchNum', $batchs);
                      }
                  })
                  ->select('B.tool_name', DB::raw('Count( res > 0 OR NULL ) AS total,
                    Count( res = 1 OR NULL ) AS passed,
                    Count( res = 2 OR NULL ) AS unpassed,
                    FORMAT( Count( res = 1 OR NULL ) * 100 / Count( res > 0 OR NULL ), 2 ) AS lv'))
                  ->groupBy('B.tool_name')
                  ->orderByRaw('total desc,unpassed desc,tool_name desc')->get();
        $echarts_data['tool_name'] = [];
        $echarts_data['total'] = [];
        $echarts_data['unpassed'] = [];
        foreach($data as $val) {
            $echarts_data['tool_name'][] = $val->tool_name;
            $echarts_data['total'][] = $val->total;
            $echarts_data['unpassed'][] = $val->unpassed;
        }
        return response()->json($echarts_data);
    }

    // echarts 合格数量 与 不合格数量 -- 按送检单位统计
    public function statisticsByClient(Request $request) {
        if($request->isMethod('POST')) {
            $date_start = $request->date_start;
            $date_end = $request->date_end;
        }
        else {
            $date_start = date("Y-m-d", strtotime('-6 months'));
            $date_end = date("Y-m-d");
        }

        $data = [];

        $sql = "SELECT
                    B.client_name AS name,
                    Count( A.res > 0 OR NULL ) AS total,
                    Count( A.res = 1 OR NULL ) AS passed,
                    Count( A.res = 2 OR NULL ) AS unpassed,
                    Count( res1 = 0 OR NULL ) AS unwai,
                    Count((A.res = 1 AND C.tool_id = 47) OR NULL) AS dou_count,
                    FORMAT( Count( A.res = 1 OR NULL ) * 100 / Count( A.res > 0 OR NULL ), 2 ) AS lv
                FROM
                    a_sample_info AS A
                    LEFT JOIN a_contract AS B ON A.contract_id = B.id
                    LEFT JOIN a_contract_gaiyao AS C ON A.contract_gaiyao_id = C.id
                    LEFT JOIN ( SELECT id, res AS res1, item_id, sample_info_id FROM a_item_data WHERE item_id = 61 ) AS C ON A.id = C.sample_info_id
                WHERE
                    A.test_time BETWEEN :date_start AND :date_end
                GROUP BY
                    B.client_name
                ORDER BY
                    total DESC,
                    unpassed DESC";
        $data['clients'] = DB::select($sql, ['date_start' => $date_start, 'date_end' => $date_end]);
        $data['count'] = count($data['clients']);
        $echarts_data = [];
        $echarts_data['name'] = [];
        $echarts_data['total'] = [];
        $echarts_data['unpassed'] = [];
        $echarts_data['unwai'] = [];
        foreach($data['clients'] as $val) {
            $echarts_data['name'][] = $val->name;
            $echarts_data['total'][] = $val->total;
            $echarts_data['unpassed'][] = $val->unpassed;
            $echarts_data['unwai'][] = $val->unwai;
        }
        $data['echarts_data'] = $echarts_data;

        //样品详情
        $clients_tools = DB::table('a_sample_info as a')
            ->leftJoin('a_contract_gaiyao as b','a.contract_gaiyao_id','=','b.id')
            ->leftJoin('a_contract as c','b.contract_id','=','c.id')
            ->select('c.client_name','b.tool_name','b.voltage','b.unit')
            ->whereBetween('a.test_time',[$date_start,$date_end])
            ->get()->toArray();
        $clients_tools2 = [];
        foreach ($clients_tools as $k => &$item) {
            $item2 = &$clients_tools2[$item->client_name][$item->tool_name][$item->voltage];
            $item2 = $item2 ?: $item;
            data_set($item2,'count',(data_get($item2,'count',0) + 1));
        }
        // dd($clients_tools2);
        $data['clients_tools'] = $clients_tools2;
        return view('admin.statistics.statisticsByClient', compact('data', 'date_start', 'date_end'));
    }

    // echarts 合格数量 与 不合格数量 -- 按工器具分类统计
    public function statisticsByToolClass(Request $request) {
        if($request->isMethod('POST')) {
            $date_start = $request->date_start;
            $date_end = $request->date_end;
        }
        else {
            $date_start = date("Y-m-d", strtotime('-6 months'));
            $date_end = date("Y-m-d");
        }
        $batchNum = $request->input('batchNum', '');

        $data = [];

        $entrusted_company = DB::table('a_client')->distinct()->pluck('entrusted_company');
        $comData = [];
        $where = [];
        if($batchNum) {
            $where['E.batchNum'] = $batchNum;
        }
        $data['data'] = DB::table('a_sample_info AS A')->leftJoin('a_contract_gaiyao AS B', 'A.contract_gaiyao_id', '=', 'B.id')->leftJoin('a_tool AS C', 'C.id', '=', 'B.tool_id')->leftJoin('a_tool_class AS D', 'C.tool_class_id', '=', 'D.id')->leftJoin('a_contract AS E', 'A.contract_id', '=', 'E.id')->selectRaw('D.name,
                    Count( res > 0 OR NULL ) AS total,
                    Count( res = 1 OR NULL ) AS passed,
                    Count( res = 2 OR NULL ) AS unpassed,
                    FORMAT( Count( res = 1 OR NULL ) * 100 / Count( res > 0 OR NULL ), 2 ) AS lv')->whereBetween('A.test_time', [$date_start, $date_end])->where($where)->groupBy('D.id')->get();
        $data['count'] = count($data['data']);
        foreach($entrusted_company as $com) {
            $where['E.entrusted_company'] = $com;
            $comData[$com] = DB::table('a_sample_info AS A')->leftJoin('a_contract_gaiyao AS B', 'A.contract_gaiyao_id', '=', 'B.id')->leftJoin('a_tool AS C', 'C.id', '=', 'B.tool_id')->leftJoin('a_tool_class AS D', 'C.tool_class_id', '=', 'D.id')->leftJoin('a_contract AS E', 'A.contract_id', '=', 'E.id')->selectRaw('D.name,
                    Count( res > 0 OR NULL ) AS total,
                    Count( res = 1 OR NULL ) AS passed,
                    Count( res = 2 OR NULL ) AS unpassed,
                    FORMAT( Count( res = 1 OR NULL ) * 100 / Count( res > 0 OR NULL ), 2 ) AS lv')->whereBetween('A.test_time', [$date_start, $date_end])->where($where)->groupBy('D.id')->get();
        }
        $data['comData'] = $comData;
        $echarts_data = [];
        $echarts_data['name'] = [];
        $echarts_data['total'] = [];
        $echarts_data['unpassed'] = [];
        $heji = [];
        $heji['totalnum'] = 0;
        $heji['unpassednum'] = 0;
        foreach($data['data'] as $val) {
            $echarts_data['name'][] = $val->name;
            $echarts_data['total'][] = $val->total;
            $echarts_data['unpassed'][] = $val->unpassed;
            $heji['totalnum'] += $val->total;
            $heji['unpassednum'] += $val->unpassed;
        }
        $heji['lv'] = $heji['totalnum'] > 0 ? round((1 - $heji['unpassednum']/$heji['totalnum'])*100, 2) : 0;
        $echarts_data = json_encode($echarts_data);
        $data['echarts_data'] = $echarts_data;
        $data['heji'] = $heji;
        return view('admin.statistics.statisticsByToolClass', compact('data', 'date_start', 'date_end', 'batchNum'));
    }

    //按工器具统计
    public function statisticsByTool(Request $request) {
        if($request->isMethod('POST')) {
            $date_start = $request->date_start;
            $date_end = $request->date_end;
        }
        else {
            $date_start = date("Y-m-d", strtotime('-6 months'));
            $date_end = date("Y-m-d");
        }
        $data = [];
        $gaiyao = DB::table('a_contract_gaiyao as a')->leftJoin('a_status_time as b', 'a.contract_id', '=', 'b.contract_id')->whereBetween('test_start', [$date_start, $date_end])->select('a.*', 'b.test_start')->orderBy('a.tool_name', 'asc')->get();
        $tongji = [];
        foreach($gaiyao as $item) {
            if(isset($tongji[$item->tool_name][$item->voltage])) {
                $tongji[$item->tool_name][$item->voltage]['unit'] = $item->unit;
                $tongji[$item->tool_name][$item->voltage]['count'] += $item->count;
            }
            else {
                $tongji[$item->tool_name][$item->voltage]['unit'] = $item->unit;
                $tongji[$item->tool_name][$item->voltage]['count'] = $item->count;
            }
        }
        $data['tongji'] = $tongji;
        $i = 0;
        $tbdata = [];
        foreach($tongji as $toolname => $item) {
            foreach($item as $vol => $value) {
                $i += 1;
                $tbdata[$i] = $value;
                $tbdata[$i]['name'] = $toolname;
                $tbdata[$i]['voltage'] = $vol;
            }
        }
        $data['tbdata'] = $tbdata;
        return view('admin.statistics.statisticsByTool', compact('data', 'date_start', 'date_end'));
    }

    //按检验员统计
    public function ByManager(Request $request) {
        if($request->isMethod('POST')) {
            $date_start = $request->date_start;
            $date_end = $request->date_end;
        }
        else {
            $date_start = date("Y-m-d", strtotime('-6 months'));
            $date_end = date("Y-m-d");
        }
        $dataTemp = DB::table('manager AS A')
                      ->leftJoin('a_item_data AS B', 'A.id', '=', 'B.manager_id')
                      ->leftJoin('a_contract AS C', 'C.id', '=', 'B.contract_id')
                      ->leftJoin('a_contract_gaiyao AS D', 'D.id', '=', 'B.contract_gaiyao_id')
                      ->selectRaw('A.realname, D.tool_name,
                    Count( distinct B.sample_info_id) AS sample_count,
                    Count( B.item_id = 1 ) AS item_count')
                      ->where([['C.is_del', '=', 0]])
                      ->whereBetween('B.utime', [$date_start, $date_end])->groupBy('B.manager_id', 'D.tool_name')->orderBy('A.realname', 'asc')->get()->toArray();
        $temp = $data = [];
        foreach($dataTemp as $val) {
            $data[$val->realname][] = $val;
            $val = (array)$val;
            if(isset($temp[$val['realname']])) {
                foreach($val as $column => $value) {
                    if(is_int($value)) {
                        $temp[$val['realname']][$column] += $value;
                    }
                }
                $temp[$val['realname']]['rowspan'] += 1;
            }
            else {
                $temp[$val['realname']] = $val;
                $temp[$val['realname']]['rowspan'] = 1;
            }
        }
        $count = count($temp);
        $echarts_data = [];
        $echarts_data['realname'] = json_encode(array_column($temp, 'realname'));
        $echarts_data['item_count'] = json_encode(array_column($temp, 'item_count'));
        $echarts_data['sample_count'] = json_encode(array_column($temp, 'sample_count'));
        return view('admin.statistics.byManager', compact('data', 'date_start', 'date_end', 'count', 'echarts_data', 'temp'));
    }
}
